{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "structural-baking",
   "metadata": {},
   "source": [
    "import cx_Oracle"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "creative-sample",
   "metadata": {},
   "source": [
    "cx_Oracle.init_oracle_client(lib_dir=r\"C:\\oracle\\instantclient_18_5\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "public-apple",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, String, DateTime, Float, Sequence,Index, create_engine, TIMESTAMP, DateTime\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "precious-religious",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('oracle://SYSTEM:vnpy@localhost:1521/XE', encoding='utf8', echo=True, max_identifier_length=128)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "usual-index",
   "metadata": {},
   "outputs": [],
   "source": [
    "Base = declarative_base()\n",
    "class DbBarData(Base):\n",
    "    __tablename__ = 'DbBarData'\n",
    "\n",
    "    symbol = Column(String(255), primary_key=True)\n",
    "    exchange = Column(String(255), primary_key=True)\n",
    "    datetime = Column(DateTime, primary_key=True)\n",
    "    interval = Column(String(255), primary_key=True)\n",
    "\n",
    "    volume = Column(Float)\n",
    "    open_interest = Column(Float)\n",
    "    open_price = Column(Float)\n",
    "    high_price = Column(Float)\n",
    "    low_price = Column(Float)\n",
    "    close_price = Column(Float)\n",
    "\n",
    "\n",
    "class DbTickData(Base):\n",
    "    __tablename__ = 'DbTickData'\n",
    "\n",
    "    symbol = Column(String(255), primary_key=True)\n",
    "    exchange = Column(String(255), primary_key=True)\n",
    "    datetime = Column(TIMESTAMP, primary_key=True)\n",
    "\n",
    "    name = Column(String(255))\n",
    "    volume = Column(Float)\n",
    "    open_interest = Column(Float)\n",
    "    last_price = Column(Float)\n",
    "    last_volume = Column(Float)\n",
    "    limit_up = Column(Float)\n",
    "    limit_down = Column(Float)\n",
    "\n",
    "    open_price = Column(Float)\n",
    "    high_price = Column(Float)\n",
    "    low_price = Column(Float)\n",
    "    pre_close = Column(Float)\n",
    "\n",
    "    bid_price_1 = Column(Float)\n",
    "    bid_price_2 = Column(Float)\n",
    "    bid_price_3 = Column(Float)\n",
    "    bid_price_4 = Column(Float)\n",
    "    bid_price_5 = Column(Float)\n",
    "\n",
    "    ask_price_1 = Column(Float)\n",
    "    ask_price_2 = Column(Float)\n",
    "    ask_price_3 = Column(Float)\n",
    "    ask_price_4 = Column(Float)\n",
    "    ask_price_5 = Column(Float)\n",
    "\n",
    "    bid_volume_1 = Column(Float)\n",
    "    bid_volume_2 = Column(Float)\n",
    "    bid_volume_3 = Column(Float)\n",
    "    bid_volume_4 = Column(Float)\n",
    "    bid_volume_5 = Column(Float)\n",
    "\n",
    "    ask_volume_1 = Column(Float)\n",
    "    ask_volume_2 = Column(Float)\n",
    "    ask_volume_3 = Column(Float)\n",
    "    ask_volume_4 = Column(Float)\n",
    "    ask_volume_5 = Column(Float)\n",
    "\n",
    "\n",
    "class DbBarOverview(Base):\n",
    "    __tablename__ = 'DbBarOverview'\n",
    "\n",
    "    symbol = Column(String(255), primary_key=True)\n",
    "    exchange = Column(String(255), primary_key=True)\n",
    "    interval = Column(String(255), primary_key=True)\n",
    "    count = Column(Integer)\n",
    "    start = Column(DateTime)\n",
    "    end = Column(DateTime)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "valued-motorcycle",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-04-09 16:48:54,808 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL\n",
      "2021-04-09 16:48:54,811 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:48:54,815 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL\n",
      "2021-04-09 16:48:54,818 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:48:54,821 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR2(60 CHAR)) AS anon_1 FROM DUAL\n",
      "2021-04-09 16:48:54,823 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:48:54,826 INFO sqlalchemy.engine.base.Engine SELECT CAST('test nvarchar2 returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL\n",
      "2021-04-09 16:48:54,828 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:48:54,830 INFO sqlalchemy.engine.base.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'\n",
      "2021-04-09 16:48:54,832 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:48:54,839 INFO sqlalchemy.engine.base.Engine SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name\n",
      "2021-04-09 16:48:54,841 INFO sqlalchemy.engine.base.Engine {'name': 'DbBarData', 'schema_name': 'SYSTEM'}\n",
      "2021-04-09 16:48:54,846 INFO sqlalchemy.engine.base.Engine SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name\n",
      "2021-04-09 16:48:54,848 INFO sqlalchemy.engine.base.Engine {'name': 'DbTickData', 'schema_name': 'SYSTEM'}\n",
      "2021-04-09 16:48:54,852 INFO sqlalchemy.engine.base.Engine SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name\n",
      "2021-04-09 16:48:54,855 INFO sqlalchemy.engine.base.Engine {'name': 'DbBarOverview', 'schema_name': 'SYSTEM'}\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "velvet-draft",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('DbBarData', MetaData(bind=None), Column('symbol', String(length=255), table=<DbBarData>, primary_key=True, nullable=False), Column('exchange', String(length=255), table=<DbBarData>, primary_key=True, nullable=False), Column('datetime', DateTime(), table=<DbBarData>, primary_key=True, nullable=False), Column('interval', String(length=255), table=<DbBarData>, primary_key=True, nullable=False), Column('volume', Float(), table=<DbBarData>), Column('open_interest', Float(), table=<DbBarData>), Column('open_price', Float(), table=<DbBarData>), Column('high_price', Float(), table=<DbBarData>), Column('low_price', Float(), table=<DbBarData>), Column('close_price', Float(), table=<DbBarData>), schema=None)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DbBarData.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "monthly-haven",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-04-09 16:48:59,961 INFO sqlalchemy.engine.base.Engine \n",
      "DROP TABLE \"DbTickData\"\n",
      "2021-04-09 16:48:59,963 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:49:00,441 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-04-09 16:49:00,443 INFO sqlalchemy.engine.base.Engine \n",
      "DROP TABLE \"DbBarData\"\n",
      "2021-04-09 16:49:00,445 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:49:00,480 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2021-04-09 16:49:00,483 INFO sqlalchemy.engine.base.Engine \n",
      "DROP TABLE \"DbBarOverview\"\n",
      "2021-04-09 16:49:00,485 INFO sqlalchemy.engine.base.Engine {}\n",
      "2021-04-09 16:49:00,520 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "DbTickData.__table__.drop(engine)\n",
    "DbBarData.__table__.drop(engine)\n",
    "DbBarOverview.__table__.drop(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "sunset-filter",
   "metadata": {},
   "outputs": [],
   "source": [
    "Database = sessionmaker(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "attached-wallpaper",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = Database()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "final-conditions",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "a=datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "minor-wilson",
   "metadata": {},
   "outputs": [],
   "source": [
    "one = DbBarData(symbol='IF888', exchange ='CFFEX', datetime =a, interval = '1h', volume = 0, open_interest =0, open_price = 0, high_price = 10, low_price =0.5, close_price = 0.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "statewide-precipitation",
   "metadata": {},
   "outputs": [],
   "source": [
    "one = DbBarOverview(symbol='IF888', exchange ='CFFEX', interval = '1m', count=10, start=a,end=a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "given-impossible",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.merge(one)\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "accessible-atmosphere",
   "metadata": {},
   "outputs": [],
   "source": [
    "b=db.query(DbBarData).filter_by(exchange='CFFEX').all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "possible-camcorder",
   "metadata": {},
   "outputs": [],
   "source": [
    "(b[0].datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "golden-produce",
   "metadata": {},
   "outputs": [],
   "source": [
    "count = db.query(DbTickData).filter(\n",
    "            DbTickData.exchange == 'BINANCE'\n",
    "        ).delete()\n",
    "\n",
    "db.commit()\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "overhead-weight",
   "metadata": {},
   "outputs": [],
   "source": [
    "for instance in db.query(DbBarData).order_by(DbBarData.datetime):\n",
    "    print(instance.interval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "mental-bleeding",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "controlling-trustee",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pytz import utc  \n",
    "from pytz import timezone  \n",
    "from datetime import datetime  \n",
    "\n",
    "cst_tz = timezone('Asia/Shanghai')  \n",
    "utc_tz = timezone('UTC')  \n",
    "\n",
    "now = datetime.now().replace(tzinfo=cst_tz)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "isolated-health",
   "metadata": {},
   "outputs": [],
   "source": [
    "now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "norwegian-prefix",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "distinguished-raleigh",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
